﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using Koonsoft.HYCRM.Model.Enum;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace Koonsoft.HYCRM.Model
{
    /// <summary>
    /// 员工访问数据库层
    /// </summary>
    public class EmpolyeeDAL
    {
        private Database database = DatabaseFactory.CreateDatabase("KOONCRMDB");

        /// <summary>
        /// 根据登录名和密码获取用户信息
        /// </summary>
        /// <param name="loginname"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        public bool CheckEmpolyeeExist(string loginname, string password)
        {
            const string sql = @"select [password] from Empolyee where loginname=@loginname and (status=0 or status = 4 or  status = 9)";

            using (var cmd = database.GetSqlStringCommand(sql))
            {
                database.AddInParameter(cmd, "@loginname", DbType.String, loginname);

                var rd = (RefCountingDataReader)database.ExecuteReader(cmd);

                var sqlrd = (SqlDataReader)rd.InnerReader;

                if (sqlrd.HasRows)
                {
                    sqlrd.Read();
                    return sqlrd["password"].ToString().Equals(password);
                }

                return false;
            }
        }

        /// <summary>
        /// 获取用户是否过期
        /// </summary>
        /// <param name="loginName"></param>
        public bool IsUserExpired(string loginName)
        {
            const string sql = "select * from Empolyee where LoginName=@loginname and [Status]=0";

            using (var cmd = database.GetSqlStringCommand(sql))
            {
                database.AddInParameter(cmd, "@loginname", DbType.String, loginName);

                DataSet ds;

                ds = database.ExecuteDataSet(cmd);

                return ds.Tables[0].Rows.Count > 0;
            }
        }

        /// <summary>
        /// 获取用户是否过期
        /// </summary>
        /// <param name="empolyeeID"></param>
        public bool IsUserExpired(int empolyeeID)
        {
            const string sql = "select * from Empolyee where empolyeeID=@empolyeeID and [Status]=0";

            using (var cmd = database.GetSqlStringCommand(sql))
            {
                database.AddInParameter(cmd, "@empolyeeID", DbType.Int32, empolyeeID);

                DataSet ds;

                ds = database.ExecuteDataSet(cmd);

                return ds.Tables[0].Rows.Count > 0;
            }
        }

        /// <summary>
        /// 获取账号信息
        /// </summary>
        /// <param name="empolyeeID"></param>
        /// <returns></returns>
        public EmpolyeeInfo GetEmpolyeeInfoByEmpolyeeID(int empolyeeID)
        {
            const string procedure = @"Empolyee_GetEmpolyeeByID";

            using (var cmd = database.GetStoredProcCommand(procedure))
            {
                database.AddInParameter(cmd, "@empolyeeid", DbType.Int32, empolyeeID);

                var rd = (RefCountingDataReader)database.ExecuteReader(cmd);
                var sqlrd = (SqlDataReader)rd.InnerReader;

                if (sqlrd.HasRows)
                {
                    sqlrd.Read();

                    var accountInfo = new EmpolyeeInfo
                    {
                        LoginName = sqlrd["LoginName"].ToString(),
                        Name = sqlrd["Name"].ToString(),
                        Password = sqlrd["password"].ToString(),
                        RoleID = sqlrd["RoleID"] == DBNull.Value ? -1 : Convert.ToInt32(sqlrd["RoleID"]),
                        RoleName = sqlrd["RoleName"] == DBNull.Value ? "" : sqlrd["RoleName"].ToString(),
                        Status = Convert.ToInt32(sqlrd["status"]),
                        EmpolyeeID = Convert.ToInt32(sqlrd["EmpolyeeID"]),
                        Barcode = sqlrd["barcode"] == DBNull.Value ? "" : sqlrd["barcode"].ToString(),
                        EntryTime = Convert.ToDateTime(sqlrd["EntryTime"]),
                        BasicWage = Convert.ToInt32(sqlrd["BasicWage"]),
                        LivingSubsidy = Convert.ToInt32(sqlrd["LivingSubsidy"]),
                        Confidential = Convert.ToInt32(sqlrd["Confidential"]),
                        Other = Convert.ToInt32(sqlrd["Other"])
                    };

                    return accountInfo;
                }

                return null;
            }
        }

        /// <summary>
        /// 获取员工所属分行编号列表
        /// </summary>
        /// <param name="empolyeeID"></param>
        /// <returns></returns>
        public List<int> GetBranchesByEmpolyeeID(int empolyeeID)
        {
            var branches = new List<int>();

            const string sql = @"select branchid from Empolyeebranch where EmpolyeeID=@empolyeeid";

            using (var cmd = database.GetSqlStringCommand(sql))
            {
                database.AddInParameter(cmd, "@empolyeeid", DbType.Int32, empolyeeID);

                var rd = (RefCountingDataReader)database.ExecuteReader(cmd);
                var sqlrd = (SqlDataReader)rd.InnerReader;

                while (sqlrd.Read())
                {
                    branches.Add(Convert.ToInt32(sqlrd["branchid"]));
                }
            }

            return branches;
        }

        /// <summary>
        /// 更新员工信息
        /// </summary>
        /// <param name="empolyeeName"></param>
        /// <param name="empolyeLoginName"></param>
        /// <param name="password"></param>
        /// <param name="status"></param>
        /// <param name="empolyeeID"></param>
        /// <param name="admin"></param>
        /// <param name="barcode"></param>
        /// <returns></returns>
        public bool UpdateEmpolyeeInfoByEmpolyeeID(string empolyeeName, string empolyeLoginName, string password,
                                                          int status, string admin, string barcode, int empolyeeID)
        {
            const string sql =
                @"update Empolyee set Name=@name,[Status]=@status,LoginName=@loginname,
            [Password]=@password,lastmodifytime=getdate(),lastmodifyoperator=@lastmodifyoperator,barcode=@barcode where EmpolyeeID=@empolyeeid";

            using (var cmd = database.GetSqlStringCommand(sql))
            {
                database.AddInParameter(cmd, "@empolyeeid", DbType.Int32, empolyeeID);
                database.AddInParameter(cmd, "@name", DbType.String, empolyeeName);
                database.AddInParameter(cmd, "@loginname", DbType.String, empolyeLoginName);
                database.AddInParameter(cmd, "@password", DbType.String, password);
                database.AddInParameter(cmd, "@status", DbType.Int32, status);
                database.AddInParameter(cmd, "@lastmodifyoperator", DbType.String, admin);
                database.AddInParameter(cmd, "@barcode", DbType.String, barcode);

                return database.ExecuteNonQuery(cmd) == 1;
            }
        }

        /// <summary>
        /// 更新用户角色
        /// </summary>
        /// <param name="empolyeeID"></param>
        /// <param name="roleID"></param>
        /// <returns></returns>
        public bool UpdateEmpolyeeRole(int empolyeeID, int roleID)
        {
            const string procedure = @"Empolyee_UpdateRole";

            using (var cmd = database.GetStoredProcCommand(procedure))
            {
                database.AddInParameter(cmd, "@empolyeeID", DbType.Int32, empolyeeID);
                database.AddInParameter(cmd, "@roleID", DbType.Int32, roleID);

                return database.ExecuteNonQuery(cmd) == 1;
            }
        }

        /// <summary>
        /// 移除分行与员工的关系
        /// </summary>
        /// <param name="empolyeeID"></param>
        /// <returns></returns>
        public static bool RemoveBranchRelation(int empolyeeID)
        {
            const string sql = @"delete from EmpolyeeBranch where EmpolyeeID=@empolyeeid";

            var sqlconn = new SqlConnection
            {
                ConnectionString = ConfigurationManager.ConnectionStrings["KOONCRMDB"].ConnectionString
            };

            var sqlcmd = new SqlCommand
            {
                CommandText = sql,
                CommandType = CommandType.Text,
                Connection = sqlconn
            };

            sqlcmd.Parameters.Add("@empolyeeid", SqlDbType.Int);
            sqlcmd.Parameters["@empolyeeid"].Value = empolyeeID;

            try
            {
                sqlcmd.Connection.Open();

                return sqlcmd.ExecuteNonQuery() == 1;
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                sqlcmd.Connection.Close();
            }
        }

        /// <summary>
        /// 新增分行与员工的关系
        /// </summary>
        /// <param name="empolyeeID"></param>
        /// <param name="branchID"></param>
        /// <returns></returns>
        public static bool AddBranchRelation(int empolyeeID, int branchID)
        {
            const string sql = @"insert into EmpolyeeBranch (EmpolyeeID,BranchID) values (@empolyeeid,@branchid)";

            var sqlconn = new SqlConnection
            {
                ConnectionString = ConfigurationManager.ConnectionStrings["KOONCRMDB"].ConnectionString
            };

            var sqlcmd = new SqlCommand
            {
                CommandText = sql,
                CommandType = CommandType.Text,
                Connection = sqlconn
            };

            sqlcmd.Parameters.Add("@empolyeeid", SqlDbType.Int);
            sqlcmd.Parameters["@empolyeeid"].Value = empolyeeID;

            sqlcmd.Parameters.Add("@branchid", SqlDbType.Int);
            sqlcmd.Parameters["@branchid"].Value = branchID;

            try
            {
                sqlcmd.Connection.Open();

                return sqlcmd.ExecuteNonQuery() == 1;
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                sqlcmd.Connection.Close();
            }
        }

        /// <summary>
        /// 根据登录名获取密码
        /// </summary>
        /// <param name="loginName"></param>
        /// <returns></returns>
        public static string GetPasswordByLoginName(string loginName)
        {
            SqlDataReader rd;

            const string sql = @"select password from Empolyee where LoginName=@loginname";

            var sqlconn = new SqlConnection
            {
                ConnectionString = ConfigurationManager.ConnectionStrings["KOONCRMDB"].ConnectionString
            };


            var sqlcmd = new SqlCommand
            {
                CommandText = sql,
                CommandType = CommandType.Text,
                Connection = sqlconn
            };

            sqlcmd.Parameters.Add("@loginname", SqlDbType.NVarChar, 16);
            sqlcmd.Parameters["@loginname"].Value = loginName;

            try
            {
                sqlcmd.Connection.Open();
                rd = sqlcmd.ExecuteReader();
                if (rd.HasRows)
                {
                    rd.Read();
                    return rd["password"].ToString();
                }
            }
            catch (Exception ex)
            {
            }
            finally
            {
                sqlcmd.Connection.Close();
            }

            return string.Empty;
        }

        /// <summary>
        /// 修改密码
        /// </summary>
        /// <param name="loginName"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        public static bool UpdatePasswordByLoginName(string loginName, string password)
        {
            const string sql = @"update Empolyee set password=@password where LoginName=@loginname";

            var sqlconn = new SqlConnection
            {
                ConnectionString = ConfigurationManager.ConnectionStrings["KOONCRMDB"].ConnectionString
            };


            var sqlcmd = new SqlCommand
            {
                CommandText = sql,
                CommandType = CommandType.Text,
                Connection = sqlconn
            };

            sqlcmd.Parameters.Add("@loginname", SqlDbType.NVarChar, 16);
            sqlcmd.Parameters["@loginname"].Value = loginName;

            sqlcmd.Parameters.Add("@password", SqlDbType.NVarChar, 16);
            sqlcmd.Parameters["@password"].Value = password;

            try
            {
                sqlcmd.Connection.Open();
                return sqlcmd.ExecuteNonQuery() == 1;
            }
            catch (Exception ex)
            {

                return false;
            }
            finally
            {
                sqlcmd.Connection.Close();
            }
        }

        /// <summary>
        /// 获取员工信息
        /// </summary>
        /// <param name="loginName"></param>
        /// <returns></returns>
        public EmpolyeeInfo GetEmpolyeeInfoByLoginName(string loginName)
        {
            SqlDataReader sqlrd;

            const string sql = @"select * from Empolyee a 
                                join EmpolyeeRole b
                                on a.EmpolyeeID=b.EmpolyeeID
                                join Role c
                                on b.RoleID=c.RoleID where loginname=@loginname";

            var sqlconn = new SqlConnection
            {
                ConnectionString = ConfigurationManager.ConnectionStrings["KOONCRMDB"].ConnectionString
            };

            var sqlcmd = new SqlCommand
            {
                CommandText = sql,
                CommandType = CommandType.Text,
                Connection = sqlconn
            };

            sqlcmd.Parameters.Add("@loginname", SqlDbType.NVarChar, 64);
            sqlcmd.Parameters["@loginname"].Value = loginName;

            try
            {
                sqlcmd.Connection.Open();
                sqlrd = sqlcmd.ExecuteReader();
                if (sqlrd.HasRows)
                {
                    sqlrd.Read();

                    return new EmpolyeeInfo
                    {
                        LoginName = sqlrd["LoginName"].ToString(),
                        Name = sqlrd["Name"].ToString(),
                        Password = sqlrd["password"].ToString(),
                        RoleID = Convert.ToInt32(sqlrd["RoleID"]),
                        RoleName = sqlrd["RoleName"].ToString(),
                        Status = Convert.ToInt32(sqlrd["status"]),
                        EmpolyeeID = Convert.ToInt32(sqlrd["EmpolyeeID"]),
                        Barcode = sqlrd["barcode"].ToString()
                    };
                }
            }
            catch (Exception ex)
            {

            }
            finally
            {
                sqlcmd.Connection.Close();
            }

            return null;
        }

        /// <summary>
        /// 根据员工ID获取员工姓名
        /// </summary>
        /// <param name="empolyeeID"></param>
        /// <returns></returns>
        public string GetEmpolyeeNameByEmpolyeeID(int empolyeeID)
        {
            const string sql = "select name from Empolyee where EmpolyeeID=@EmpolyeeID";

            using (var cmd = database.GetSqlStringCommand(sql))
            {
                database.AddInParameter(cmd, "@EmpolyeeID", DbType.Int32, empolyeeID);

                return database.ExecuteScalar(cmd).ToString();
            }
        }

        public bool AddNewEmpolyee(string name, string loginName, string password, string creator, string barcode)
        {
            const string sql = @"insert into Empolyee (Name,Status,Loginname,Password,CreateTime,Creator,Barcode) values 
                            (@Name,0,@Loginname,@Password,getdate(),@Creator,@Barcode)";

            using (var cmd = database.GetSqlStringCommand(sql))
            {
                database.AddInParameter(cmd, "@Name", DbType.String, name);
                database.AddInParameter(cmd, "@Loginname", DbType.String, loginName);
                database.AddInParameter(cmd, "@Password", DbType.String, password);
                database.AddInParameter(cmd, "@Creator", DbType.String, creator);
                database.AddInParameter(cmd, "@Barcode", DbType.String, barcode);

                return database.ExecuteNonQuery(cmd) == 1;
            }
        }


        public bool AddNewEmpolyee(EmpolyeeInfo empolyee)
        {
            const string sql = @"CreateEmpolyee";

            using (var cmd = database.GetStoredProcCommand(sql))
            {
                database.AddInParameter(cmd, "@xml", DbType.String, XmlObjectHelper.ObjectToXML(empolyee));

                return database.ExecuteNonQuery(cmd) > 0;
            }
        }

        public bool UpdateEmpolyee(EmpolyeeInfo empolyee)
        {
            const string sql = @"UpdateEmpolyee";

            using (var cmd = database.GetStoredProcCommand(sql))
            {
                database.AddInParameter(cmd, "@xml", DbType.String, XmlObjectHelper.ObjectToXML(empolyee));

                return database.ExecuteNonQuery(cmd) > 0;
            }
        }

        public bool UpdateStatus(int id, int status)
        {
            const string sql = @"Update Empolyee set Status = @Status where EmpolyeeId = @id";

            using (var cmd = database.GetSqlStringCommand(sql))
            {
                database.AddInParameter(cmd, "@id", DbType.Int32, id);
                database.AddInParameter(cmd, "@status", DbType.Int32, status);

                return database.ExecuteNonQuery(cmd) == 1;
            }
        }

        /// <summary>
        /// 更新员工信息
        /// </summary>
        /// <param name="empolyeeName"></param>
        /// <param name="empolyeLoginName"></param>
        /// <param name="password"></param>
        /// <param name="status"></param>
        /// <param name="empolyeeID"></param>
        /// <param name="admin"></param>
        /// <param name="barcode"></param>
        /// <returns></returns>
        public bool UpdateEmpolyeeInfoByEmpolyeeID(string empolyeeName, string empolyeLoginName, string admin, string barcode, int empolyeeID, DateTime entryTime)
        {
            const string sql =
                @"update Empolyee set Name=@name, LoginName=@loginname,
             lastmodifytime=getdate(),lastmodifyoperator=@lastmodifyoperator,barcode=@barcode
                ,entryTime=@entryTime where EmpolyeeID=@empolyeeid";

            using (var cmd = database.GetSqlStringCommand(sql))
            {
                database.AddInParameter(cmd, "@empolyeeid", DbType.Int32, empolyeeID);
                database.AddInParameter(cmd, "@name", DbType.String, empolyeeName);
                database.AddInParameter(cmd, "@loginname", DbType.String, empolyeLoginName);
                database.AddInParameter(cmd, "@lastmodifyoperator", DbType.String, admin);
                database.AddInParameter(cmd, "@barcode", DbType.String, barcode);
                database.AddInParameter(cmd, "@entryTime", DbType.DateTime, entryTime);

                return database.ExecuteNonQuery(cmd) == 1;
            }
        }

        public List<EmplyeeContract> GetEmplyeeContractByEmplyeeId(int emplyeeId)
        {
            var emplyeeContracts = new List<EmplyeeContract>();

            const string sql = "select * from EmplyeeContracts where EmplyeeId =@EmplyeeId order by ID desc";

            using (var cmd = database.GetSqlStringCommand(sql))
            {
                database.AddInParameter(cmd, "@EmplyeeId", DbType.Int32, emplyeeId);

                var rd = (RefCountingDataReader)database.ExecuteReader(cmd);

                var sqlrd = (SqlDataReader)rd.InnerReader;

                while (sqlrd.Read())
                {
                    var item = new EmplyeeContract
                    {
                        ContractId = sqlrd["ContractId"].ToString(),
                        id = Convert.ToInt32(sqlrd["ID"]),
                        EmplyeeId = Convert.ToInt32(sqlrd["EmplyeeId"]),
                        Status = Convert.ToInt32(sqlrd["Status"]),
                        Path = sqlrd["Path"].ToString()
                    };

                    if (!Convert.IsDBNull(sqlrd["StartTime"]))
                    {
                        item.StartTime = Convert.ToDateTime(sqlrd["StartTime"]);
                    }

                    if (!Convert.IsDBNull(sqlrd["EndTime"]))
                    {
                        item.EndTime = Convert.ToDateTime(sqlrd["EndTime"]);
                    }

                    emplyeeContracts.Add(item);
                }
            }

            return emplyeeContracts;
        }

        public void CancelEmplyeeContracts(int emplyeeId)
        {
            const string sql = @"Update EmplyeeContracts set Status = @newStatus where EmplyeeId = @EmpolyeeId and Status = @Status";

            using (var cmd = database.GetSqlStringCommand(sql))
            {
                database.AddInParameter(cmd, "@EmpolyeeId", DbType.Int32, emplyeeId);
                database.AddInParameter(cmd, "@status", DbType.Int32, (int)EmplyeeContractStatus.Create);
                database.AddInParameter(cmd, "@newStatus", DbType.Int32, (int)EmplyeeContractStatus.Cancel);
                database.ExecuteNonQuery(cmd);
            }
        }

        public bool ConfirmEmplyeeContract(int emplyeeId)
        {
            const string sql = @"Update EmplyeeContracts set Status = @newStatus where EmplyeeId = @EmpolyeeId and Status = @Status";

            using (var cmd = database.GetSqlStringCommand(sql))
            {
                database.AddInParameter(cmd, "@EmpolyeeId", DbType.Int32, emplyeeId);
                database.AddInParameter(cmd, "@status", DbType.Int32, (int)EmplyeeContractStatus.Create);
                database.AddInParameter(cmd, "@newStatus", DbType.Int32, (int)EmplyeeContractStatus.Effective);
                return database.ExecuteNonQuery(cmd) > 0;
            }
        }

        public bool AddEmplyeeContracts(int empolyeeId, string path, string contractId, DateTime startTime, DateTime endTime)
        {
            const string sql = @"insert into EmplyeeContracts (EmplyeeId,Status,path,[ContractId],StartTime,EndTime) 
                            values (@EmpolyeeId,@Status,@path,@ContractId,@StartTime,@EndTime)";

            using (var cmd = database.GetSqlStringCommand(sql))
            {
                database.AddInParameter(cmd, "@EmpolyeeId", DbType.Int32, empolyeeId);
                database.AddInParameter(cmd, "@Status", DbType.Int32, (int)EmplyeeContractStatus.Effective);
                database.AddInParameter(cmd, "@path", DbType.String, path);
                database.AddInParameter(cmd, "@ContractId", DbType.String, contractId);
                database.AddInParameter(cmd, "@StartTime", DbType.DateTime, startTime);
                database.AddInParameter(cmd, "@EndTime", DbType.DateTime, endTime);

                return database.ExecuteNonQuery(cmd) == 1;
            }
        }


        /// <summary>
        /// 获取账号信息
        /// </summary>
        /// <param name="empolyeeID"></param>
        /// <returns></returns>
        public EmpolyeeInfoExpansion GetEmpolyeeExpansionByEmpolyeeID(int empolyeeID)
        {
            const string procedure = @"select * from EmpolyeeExpansion where empolyeeID = @empolyeeID";

            using (var cmd = database.GetSqlStringCommand(procedure))
            {
                database.AddInParameter(cmd, "@empolyeeID", DbType.Int32, empolyeeID);

                var rd = (RefCountingDataReader)database.ExecuteReader(cmd);
                var sqlrd = (SqlDataReader)rd.InnerReader;

                if (sqlrd.HasRows)
                {
                    sqlrd.Read();

                    var accountInfo = new EmpolyeeInfoExpansion
                    {
                        Sex = Convert.ToInt32(sqlrd["Sex"]),
                        Birthday = Convert.ToDateTime(sqlrd["Birthday"]),
                        Phone = sqlrd["Phone"].ToString(),
                        Address = sqlrd["Address"].ToString(),
                        IdCard = sqlrd["IdCard"].ToString()
                    };

                    return accountInfo;
                }

                return null;
            }
        }


        public bool CreateLoginLog(string loginNamd, int userStatus)
        {
            const string sql = @"insert into UserLoginLog (LoginName,UserStatus) 
                            values (@LoginName,@UserStatus)";

            using (var cmd = database.GetSqlStringCommand(sql))
            {
                database.AddInParameter(cmd, "@UserStatus", DbType.Int32, userStatus);
                database.AddInParameter(cmd, "@LoginName", DbType.String, loginNamd);

                return database.ExecuteNonQuery(cmd) == 1;
            }
        }


        /// <summary>
        /// 获取账号信息
        /// </summary>
        /// <param name="empolyeeID"></param>
        /// <returns></returns>
        public void ChangeEmpolyeeWage(int empolyeeID, int basicWage, int LivingSubsidy, int Confidential, int Other, string remark, string Operator)
        {
            const string procedure = @"Empolyee_ChangeEmpolyeeWage";

            using (var cmd = database.GetStoredProcCommand(procedure))
            {
                database.AddInParameter(cmd, "@empolyeeid", DbType.Int32, empolyeeID);
                database.AddInParameter(cmd, "@basicWage", DbType.Int32, basicWage);
                database.AddInParameter(cmd, "@LivingSubsidy", DbType.Int32, LivingSubsidy);
                database.AddInParameter(cmd, "@Confidential", DbType.Int32, Confidential);
                database.AddInParameter(cmd, "@Other", DbType.Int32, Other);
                database.AddInParameter(cmd, "@remark", DbType.String, remark);
                database.AddInParameter(cmd, "@Operator", DbType.String, Operator);

                database.ExecuteNonQuery(cmd);
            }
        }


        public List<ChangeEmpolyeeWageEntity> GetChangeEmpolyeeWageLogByEmplyeeId(int emplyeeId)
        {
            var changeEmpolyeeWageEntity = new List<ChangeEmpolyeeWageEntity>();

            const string sql = "select * from ChangeEmpolyeeWageLog where EmpolyeeId =@EmpolyeeId order by ID desc";

            using (var cmd = database.GetSqlStringCommand(sql))
            {
                database.AddInParameter(cmd, "@EmpolyeeId", DbType.Int32, emplyeeId);

                var rd = (RefCountingDataReader)database.ExecuteReader(cmd);

                var sqlrd = (SqlDataReader)rd.InnerReader;

                while (sqlrd.Read())
                {
                    var item = new ChangeEmpolyeeWageEntity()
                    {
                        ChangeTime = Convert.ToDateTime(sqlrd["ChangeTime"]),
                        Empolyeeid = Convert.ToInt32(sqlrd["EmpolyeeId"]),
                        Remark = sqlrd["Remark"].ToString(),
                        Operator = sqlrd["Operator"].ToString()
                    };
                    changeEmpolyeeWageEntity.Add(item);
                }
            }

            return changeEmpolyeeWageEntity;
        }
    }

}
